create  table jms_dm.dm_network_retention_summary_dt
(
end_agent_code   varchar(100) COMMENT '末端网点所属代理区编码' ,
end_franchisee_code   varchar(100) COMMENT '末端网点所属加盟商编码' ,
final_sign_network_code   varchar(100) COMMENT '最终签收派件网点编码' ,
is_contain_difficult   varchar(100) COMMENT '是否包含问题件 1 是 2 否' ,
end_manage_region_code  varchar(100) COMMENT '末端网点所属管理大区编码' ,
end_manage_region_name   varchar(100) COMMENT '末端网点所属管理大区名称' ,
end_agent_name   varchar(100) COMMENT '末端网点所属代理区名称' ,
end_provider_id   varchar(100) COMMENT '末端网点所属省份id' ,
end_provider_name   varchar(100) COMMENT '末端网点所属省份名称' ,
end_city_id   varchar(100) COMMENT '末端网点所属城市id' ,
end_city_name   varchar(100) COMMENT '末端网点所属城市名称' ,
end_area_id     varchar(100) COMMENT '末端网点所属区县id' ,
end_area_name   varchar(100) COMMENT '末端网点所属区县名称' ,
end_franchisee_name   varchar(100) COMMENT '末端网点所属加盟商名称' ,
final_sign_network_name   varchar(100) COMMENT '最终签收派件网点名称' ,
order_source_name      varchar(100) COMMENT '订单来源' ,
order_source_code      varchar(100) COMMENT '订单来源code' ,
need_sign_count_1d   bigint COMMENT '1天应签收' ,
retention_count_1d   bigint COMMENT '1天内滞留' ,
need_sign_count_2d   bigint COMMENT '2天应签收' ,
retention_count_2d   bigint COMMENT '2天内滞留' ,
need_sign_count_3d   bigint COMMENT '3天应签收' ,
retention_count_3d   bigint COMMENT '3天内滞留' ,
need_sign_count_5d   bigint COMMENT '5天应签收' ,
retention_count_5d   bigint COMMENT '5天内滞留' ,
need_sign_count_7d   bigint COMMENT '7天应签收' ,
retention_count_7d   bigint COMMENT '7天内滞留' ,
need_sign_count_10d  bigint COMMENT '10天应签收' ,
retention_count_10d  bigint COMMENT '10天内滞留' ,
need_sign_count_15d  bigint COMMENT '15天应签收' ,
retention_count_15d  bigint COMMENT '15天内滞留' ,
need_sign_count_20d  bigint COMMENT '20天应签收' ,
retention_count_20d  bigint COMMENT '20天内滞留' ,
final_plan_sign_date   date COMMENT '规划签收日期',
third_code varchar(100) comment'三段码code',
third_name varchar(100) comment'三段码名字',
need_sign_over_20d int comment '超20天应签收',
retention_over_20d int comment '超20天内滞留'
 )
ENGINE=OLAP
DUPLICATE KEY(end_agent_code,end_franchisee_code,final_sign_network_code)
comment '网点滞留件汇总新'
PARTITION BY RANGE (final_plan_sign_date) (
   START ("2023-01-08") END ("2023-03-09") EVERY (INTERVAL 1 day))DISTRIBUTED BY HASH(final_sign_network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);
    -- Doris 表修改语句
   alter table jms_dm.dm_network_retention_summary_dt add column (
--         retention_count_4d int(8) comment '4天内滞留',
--         need_sign_count_4d int(8) comment '4天应签收',
--         retention_count_6d int(8) comment '6天内滞留',
--         need_sign_count_6d int(8) comment '6天应签收',
--         need_sign_over_7d  int(8) comment '超7天应签收',
--         retention_over_7d  int(8) comment '超7天内滞留'
    )  ;